# -*- coding: utf-8 -*-
##############################################################################
#
#    OpenERP, Open Source Management Solution
#    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU Affero General Public License as
#    published by the Free Software Foundation, either version 3 of the
#    License, or (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU Affero General Public License for more details.
#
#    You should have received a copy of the GNU Affero General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
##############################################################################
import xlwt
import math
import cStringIO
from .. import format_common
from openerp.tools.translate import _
from openerp.osv import osv, fields
import time
import base64
from openerp.tools import float_round, float_is_zero, float_compare
import logging
from babel.numbers import format_decimal
_logger = logging.getLogger(__name__)
from xlwt import *

class param_trial_balance_report(osv.osv_memory):
    _name = 'param.trial.balance.report'
    _description = 'Param Trial Balance Report'

    _columns = {
        'chart_account_id': fields.many2one('account.account', 'Chart of Account', help='Select Charts of Accounts', required=True, domain = [('parent_id','=',False)]),
        'target_move': fields.selection([('posted', 'All Posted Entries'),('all', 'All Entries'),], 'Target Moves', required=True),
        'acc_search_vals': fields.selection([('code','Account Code'),('name', 'Account Name')],'Account Search Values', required=True),
        'filter_selection': fields.selection([('all_vall','All'),('def','Default'),('input', 'Input'),('selection','Selection')],'Acc Filter Selection', required=True),
        'account_default_from':fields.many2one('account.account', 'Account From', domain=[('type','<>','view')], required=False),
        'account_default_to':fields.many2one('account.account', 'Account To', domain=[('type','<>','view')], required=False),
        'account_input_from': fields.char('Account From', size=128),
        'account_input_to': fields.char('Account To', size=128),
        'account_ids' :fields.many2many('account.account', 'acc_trial_balance_rel', 'report_id', 'partner_id', 'Account', domain=[('type','<>','view')]),
        'date_selection': fields.selection([('none_sel','None'),('period_sel','Period'),('date_sel', 'Date')],'Type Selection', required=True),
        'period_filter_selection': fields.selection([('def','Default'),('input', 'Input')],'Period Filter Selection'),
        'date_to': fields.date("To Date"),
        'period_default_to':fields.many2one('account.period', 'Period To'),
        'period_input_to': fields.char('Period To', size=128),

    }

    def _get_account(self, cr, uid, context=None):
        accounts = self.pool.get('account.account').search(cr, uid, [('parent_id', '=', False)], limit=1)
        return accounts and accounts[0] or False

    _defaults = {
        'date_selection': 'none_sel',
        'acc_search_vals': 'code',
        'filter_selection': 'all_vall',
        'chart_account_id': _get_account,
        'target_move': 'all',
        
    }

    def onchange_date_selection(self, cr, uid, ids, date_selection, context=None):
        if context is None:
            context = {}
        res = {}
        if date_selection:
            if date_selection == 'period_sel':
                res['value'] = {'period_filter_selection': 'def',
                                 }
            else:
                res['value'] = {'period_filter_selection': False,
                                 }
        return res

    def create_vat(self, cr, uid, ids, context=None):
        if context is None:
            context = {}
        datas = {'ids': context.get('active_ids', [])}
        datas['model'] = 'param.trial.balance.report'
        datas['form'] = self.read(cr, uid, ids)[0]
        return {
            'type': 'ir.actions.report.xml',
            'report_name': 'trial.balance.report_landscape',
            'datas': datas,
        }
        
    def print_report(self, cr, uid, ids, context=None):
        if context is None:
            context = {}
        data = {}
        data['ids'] = context.get('active_ids', [])
        data['model'] = context.get('active_model', 'ir.ui.menu')
        data['form'] = self.read(cr, uid, ids, ['chart_account_id', 'target_move', 'acc_search_vals','filter_selection','account_default_from','account_default_to','account_input_from','account_input_to', \
                                                'account_ids', 'date_selection', 'period_filter_selection','date_to','period_default_to','period_input_to'
                                                ], context=context)[0]
        for field in ['chart_account_id', 'target_move', 'acc_search_vals','filter_selection','account_default_from','account_default_to','account_input_from','account_input_to', \
                                                'account_ids', 'date_selection', 'period_filter_selection','date_to','period_default_to','period_input_to']:
            if isinstance(data['form'][field], tuple):
                data['form'][field] = data['form'][field][0]
        used_context = self._build_contexts(cr, uid, ids, data, context=context)

        return self._get_tplines_xls(cr, uid, ids, used_context,context=context)
    
    def _build_contexts(self, cr, uid, ids, data, context=None):
        if context is None:
            context = {}
        result = {}
        res_account_obj = self.pool.get('account.account')
        period_obj = self.pool.get('account.period')
        qry_acc = ''
        val_acc = []
        account_ids = False

        acc_selection = 'Account'
        qry_acc = "type <> 'view'"
        val_acc.append(('type', '<>', 'view'))

        account_ids = False
        account_selection = False

        result['chart_account_id'] = data['form']['chart_account_id'] and data['form']['chart_account_id']or False
        chart_account_name = data['form']['chart_account_id'] and data['form']['chart_account_id'] or False
        
        result['target_move'] = data['form']['target_move'] or False
        result['target_move_name'] = data['form']['target_move'] or False
        
        account_default_from = data['form']['account_default_from'] and data['form']['account_default_from'] or False
        account_default_to = data['form']['account_default_to'] and data['form']['account_default_to'] or False
        account_input_from = data['form']['account_input_from'] or False
        account_input_to = data['form']['account_input_to'] or False
        
        account_default_from_str = account_default_to_str = ''
        account_input_from_str = account_input_to_str= ''
        data_search = data['form']['acc_search_vals']
        
        if data_search == 'code':
            result['data_search_output'] = 'Code'
            if data['form']['filter_selection'] == 'all_vall':
                account_ids = res_account_obj.search(cr, uid, val_acc, order='code ASC')
            if data['form']['filter_selection'] == 'def':
                data_found = False
                if account_default_from and res_account_obj.browse(cr, uid, account_default_from) and res_account_obj.browse(cr, uid, account_default_from).code:
                    account_default_from_str = res_account_obj.browse(cr, uid, account_default_from).code
                    data_found = True
                    val_acc.append(('code', '>=', res_account_obj.browse(cr, uid, account_default_from).code))
                if account_default_to and res_account_obj.browse(cr, uid, account_default_to) and res_account_obj.browse(cr, uid, account_default_to).code:
                    account_default_to_str = res_account_obj.browse(cr, uid, account_default_to).code
                    data_found = True
                    val_acc.append(('code', '<=', res_account_obj.browse(cr, uid, account_default_to).code))
                account_selection = '"' + account_default_from_str + '" - "' + account_default_to_str + '"'
                if data_found:
                    account_ids = res_account_obj.search(cr, uid, val_acc, order='code ASC')
            elif data['form']['filter_selection'] == 'input':
                data_found = False
                if account_input_from:
                    account_input_from_str = account_input_from
                    cr.execute("select code " \
                                    "from account_account "\
                                    "where " + qry_acc + " and " \
                                    "code ilike '" + str(account_input_from) + "%' " \
                                    "order by code limit 1")
                    qry = cr.dictfetchone()
                    if qry:
                        data_found = True
                        val_acc.append(('code', '>=', qry['code']))
                if account_input_to:
                    account_input_to_str = account_input_to
                    cr.execute("select code " \
                                    "from account_account "\
                                    "where " + qry_acc + " and " \
                                    "code ilike '" + str(account_input_to) + "%' " \
                                    "order by code desc limit 1")
                    qry = cr.dictfetchone()
                    if qry:
                        data_found = True
                        val_acc.append(('code', '<=', qry['code']))
                #print val_part
                account_selection = '"' + account_input_from_str + '" - "' + account_input_to_str + '"'
                if data_found:
                    account_ids = res_account_obj.search(cr, uid, val_acc, order='code ASC')
            elif data['form']['filter_selection'] == 'selection':
                acc_ids = ''
                if data['form']['account_ids']:
                    for aco in  res_account_obj.browse(cr, uid, data['form']['account_ids']):
                        acc_ids += '"' + str(aco.name) + '",'
                    account_ids = data['form']['account_ids']
                account_selection = '[' + acc_ids +']'
        elif data_search == 'name':
            result['data_search_output'] = 'Name'
            if data['form']['filter_selection'] == 'all_vall':
                account_ids = res_account_obj.search(cr, uid, val_acc, order='name ASC')
            if data['form']['filter_selection'] == 'def':
                data_found = False
                if account_default_from and res_account_obj.browse(cr, uid, account_default_from) and res_account_obj.browse(cr, uid, account_default_from).name:
                    account_default_from_str = res_account_obj.browse(cr, uid, account_default_from).name
                    data_found = True
                    val_acc.append(('name', '>=', res_account_obj.browse(cr, uid, account_default_from).name))
                if account_default_to and res_account_obj.browse(cr, uid, account_default_to) and res_account_obj.browse(cr, uid, account_default_to).name:
                    account_default_to_str = res_account_obj.browse(cr, uid, account_default_to).name
                    data_found = True
                    val_acc.append(('name', '<=', res_account_obj.browse(cr, uid, account_default_to).name))
                account_selection = '"' + account_default_from_str + '" - "' + account_default_to_str + '"'
                if data_found:
                    account_ids = res_account_obj.search(cr, uid, val_acc, order='name ASC')
            elif data['form']['filter_selection'] == 'input':
                data_found = False
                if account_input_from:
                    account_input_from_str = account_input_from
                    cr.execute("select name " \
                                    "from account_account "\
                                    "where " + qry_acc + " and " \
                                    "name ilike '" + str(account_input_from) + "%' " \
                                    "order by name limit 1")
                    qry = cr.dictfetchone()
                    if qry:
                        data_found = True
                        val_acc.append(('name', '>=', qry['name']))
                if account_input_to:
                    account_input_to_str = account_input_to
                    cr.execute("select name " \
                                    "from account_account "\
                                    "where " + qry_acc + " and " \
                                    "name ilike '" + str(account_input_to) + "%' " \
                                    "order by name desc limit 1")
                    qry = cr.dictfetchone()
                    if qry:
                        data_found = True
                        val_acc.append(('name', '<=', qry['name']))
                account_selection = '"' + account_input_from_str + '" - "' + account_input_to_str + '"'
                if data_found:
                    account_ids = res_account_obj.search(cr, uid, val_acc, order='name ASC')
            elif data['form']['filter_selection'] == 'selection':
                acc_ids = ''
                if data['form']['account_ids']:
                    for aco in  res_account_obj.browse(cr, uid, data['form']['account_ids']):
                        acc_ids += '"' + str(aco.name) + '",'
                    account_ids = data['form']['account_ids']
                account_selection = '[' + acc_ids +']'
        
        result['account_ids'] = account_ids
        account_selection = account_selection
#        period_default_from = data['form']['period_default_from'] and data['form']['period_default_from'][0] or False
#        period_default_from = period_default_from and period_obj.browse(self.cr, self.uid, period_default_from) or False
        period_default_to = data['form']['period_default_to'] and data['form']['period_default_to'] or False
        period_default_to = period_default_to and period_obj.browse(cr, uid, period_default_to) or False

#        period_input_from = data['form']['period_input_from'] or False
        period_input_to = data['form']['period_input_to'] or False

        if data['form']['date_selection'] == 'none_sel':
            result['period_ids'] = False
            result['date_to'] = False
            result['date_search'] = ''
        elif data['form']['date_selection'] == 'period_sel':
            result['date_search'] = 'period'
            val_period = []
            period_to_txt = ''
            if data['form']['period_filter_selection'] == 'def':
                if period_default_to and period_default_to.date_start:
                    period_to_txt = period_default_to.code
                    val_period.append(('date_start', '<=', period_default_to.date_start))
                result['period_ids'] = period_obj.search(cr, uid, val_period)
            elif data['form']['period_filter_selection'] == 'input':
                if period_input_to:
                    period_to_txt = period_input_to
                    cr.execute("select code " \
                                    "from account_period "\
                                    "where " \
                                    "code ilike '" + str(period_input_to) + "%' " \
                                    "order by code limit 1")
                    qry = cr.dictfetchone()
                    if qry:
                        val_period.append(('code', '<=', qry['code']))
                result['period_ids'] = period_obj.search(cr, uid, val_period)
            result['date_showing'] = 'Period To : ' + period_to_txt
            result['date_to'] = False
        else:
            result['period_ids'] = False
            result['date_search'] = 'date'
            result['date_showing'] = 'Date To :' + data['form']['date_to']
            result['date_to'] = data['form']['date_to'] and data['form']['date_to'] + ' ' + '23:59:59'

        #result['account_ids'] = account_ids
        result['account_selection'] = account_selection
        
        return result
    
    def _get_tplines_xls(self, cr, uid, ids,data,context):

        form = data
        print form
        if not ids:
            ids = data['ids']
        if not ids:
            return []
        cr = cr
        uid = uid
        period_obj = self.pool.get('account.period')
        aml_obj = self.pool.get('account.move.line')
        am_obj  = self.pool.get('account.move')
        account_obj = self.pool.get('account.account')
        company_obj = self.pool.get('res.company')
        user_obj = self.pool.get('res.users')
        dp_obj = self.pool.get('decimal.precision')
        debit_total = 0.00
        credit_total = 0.00
        open_balance = 0.00
        balance = 0.00
        results = []
        
        target_move_name = form['target_move'] or False
        if target_move_name == 'posted':
            target_move_name_str = 'Target Moves : All Posted Entries'
        else:
            target_move_name_str = 'Target Moves : All Entries'
        
        
        account_selection = form['account_selection'] or False
        data_search_output = form['data_search_output'] or False

        
        date_search = form['date_search'] or ''
        
        date_showing_str = False
        if date_search == 'date':
            date_showing = form['date_showing'] or ''
            if date_showing:
                date_showing_str = date_showing
        elif date_search == 'period':
            date_showing = form['date_showing'] or ''
            if date_showing:
                date_showing_str = date_showing    
            
        chart_account_id = form['chart_account_id'] or False
        target_move = form['target_move'] or False
        print target_move
        period_ids = form['period_ids'] or False
        
        date_to = form['date_to'] or False
        account_ids = form['account_ids'] or False
                
        workbook = xlwt.Workbook()
        M_header_tstyle = format_common.font_style(position='center', bold=1, border=3, fontos='black', font_height=300)
        report_tstyle = format_common.font_style(position='left', bold=1, fontos='black', font_height=190)
        report_tstyle1 = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        header_tstyle = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        header_tstyle_c = format_common.font_style(position='center', bold=1, fontos='black', font_height=180)
        header_tstyle_l = format_common.font_style(position='left', bold=1,border=7, fontos='black', font_height=180)
        header_tstyle_c_Top = format_common.font_style(position='center', bold=1, border=4, fontos='black', font_height=180)
        header_tstyle_c_Bottom = format_common.font_style(position='center', bold=1, border=3, fontos='black', font_height=180)
        header_tstyle_l_Top = format_common.font_style(position='left', bold=1, border=4, fontos='black', font_height=180)
        header_tstyle_l_Bottom = format_common.font_style(position='left', bold=1, border=3, fontos='black', font_height=180)
        header_tstyle_r_Top = format_common.font_style(position='right', bold=1, border=4, fontos='black', font_height=180)
        header_tstyle_r_Bottom = format_common.font_style(position='right', bold=1, border=3, fontos='black', font_height=180)
        header_tstyle_r = format_common.font_style(position='right', bold=1,  border=7,fontos='black', font_height=180)
        view_tstyle = format_common.font_style(position='left', fontos='black', font_height=180)
        view_tstyle_c = format_common.font_style(position='center', fontos='black', font_height=180)
        view_tstyle_l = format_common.font_style(position='left', fontos='black', font_height=180)
        view_tstyle_r = format_common.font_style(position='right', fontos='black', font_height=180)
        view_tstyle_r_b = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        other_tstyle = format_common.font_style(position='left', fontos='black', font_height=180)
        other_tstyle_c = format_common.font_style(position='center', fontos='black', font_height=180)
        other_tstyle_r = format_common.font_style(position='right', fontos='black', font_height=180)
        text_tstyle = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        text_tstyle1 = format_common.font_style(position='left', bold=1, fontos='black', font_height=220)
        text_tstyle2 = format_common.font_style(position='left', fontos='black', font_height=220)
        text_tstyle_c = format_common.font_style(position='center', bold=1, fontos='black', font_height=180)
        text_tstyle_l = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        text_tstyle_r = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        text_tstyle_r_closing = format_common.font_style(position='right', bold=1,border=7, fontos='black', font_height=180)
        text_tstyle_Top = format_common.font_style(position='left', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Bottom = format_common.font_style(position='left', bold=1, border=3, fontos='black', font_height=180)
        text_tstyle_Top_r = format_common.font_style(position='right', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Top_l = format_common.font_style(position='left', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Bottom_r = format_common.font_style(position='right', bold=1, border=3, fontos='black', font_height=180)
        text_tstyle_TopBottom = format_common.font_style(position='left', bold=1, border=2, fontos='black', font_height=180)
        text_tstyle_TopBottom_r = format_common.font_style(position='right', bold=1, border=2, fontos='black', font_height=180)
         ## decimat format number
        decimal_format = easyxf(num_format_str = '0.000000')
        dp_ids = dp_obj.search(cr, uid, [('name','=','Account')])
        dp_id = dp_obj.browse(cr, uid, dp_ids[0])
        digits = int(math.pow(10, dp_id.digits))
        digit_output = '#,##0.' + str(digits)[1:]

        currency_format = easyxf(num_format_str = digit_output)
        
        credit_limit_format = easyxf('font: bold 1; border: top thin')
        credit_limit_format.num_format_str = digit_output
        
        currency_ttl_format = easyxf('font: bold 1')
        currency_ttl_format.num_format_str = digit_output
        
        currency_ttl_format_top = easyxf('font: bold 1; border: top thick')
        currency_ttl_format_top.num_format_str = digit_output
        
        currency_ttl_format_top_right = easyxf('font: bold 1; border: top thick; alignment: horizontal right')
        currency_ttl_format_top_right.num_format_str = digit_output
        
        currency_ttl_format_bottom = easyxf('font: bold 1; border: bottom thick')
        currency_ttl_format_bottom.num_format_str = digit_output
        
        currency_ttl_format_bottom_right = easyxf('font: bold 1; border: bottom thick; alignment: horizontal right')
        currency_ttl_format_bottom_right.num_format_str = digit_output
        #### decimat format number
                
        #partner_qry = (partner_ids and ((len(partner_ids) == 1 and "AND l.partner_id = " + str(partner_ids[0]) + " ") or "AND l.partner_id IN " + str(tuple(partner_ids)) + " ")) or "AND l.partner_id IN (0) "
        
        sheet = workbook.add_sheet('TB XLS')
        sheet.row(0).height = 350
            
        #write_merge(r1, r2, c1, c2, label, style)
        row = 0
        company_name = user_obj.browse(cr, uid, uid).company_id.name
        sheet.write_merge(row, row, 0, 4, company_name, M_header_tstyle)
        
        #sheet.row(1).height = 400
        row+=1
        sheet.write_merge(row, row, 0, 4,'Pre Post Trial Balance Report', report_tstyle)
        row+=1
        sheet.write_merge(row, row,  0, 4,str(target_move_name_str), report_tstyle)
        if account_selection:
            account_selection_str = 'Account '+ data_search_output  +' Search : ' + account_selection
            row+=1
            sheet.write_merge(row, row,  0, 4,str(account_selection_str), report_tstyle)
        if date_showing_str:
            row+=1
            sheet.write_merge(row, row,  0, 4,str(date_showing_str), report_tstyle)
        
        row+=1           
        sheet.row(row).height = 100
        sheet.write_merge(row, row, 0, 4, '', text_tstyle_c)
        #Header Name
        row+=1
        sheet.write(row,0, 'GL Acct No', currency_ttl_format_top)
        sheet.write(row,1, 'Account Description', currency_ttl_format_top)
        sheet.write(row,2, 'Opening Bal.', currency_ttl_format_top_right)
        sheet.write(row,3, 'Current Debit', currency_ttl_format_top_right)
        sheet.write(row,4, 'Balance', currency_ttl_format_top_right)
        
        row+=1
        sheet.write(row,0, '', currency_ttl_format_bottom)
        sheet.write(row,1, '', currency_ttl_format_bottom)
        sheet.write(row,2, '', currency_ttl_format_bottom)
        sheet.write(row,3, 'Current Credit', currency_ttl_format_bottom_right)
        sheet.write(row,4, '', currency_ttl_format_bottom)
                
        #Column Width
        sheet.col(0).width = 256 * 22
        sheet.col(1).width = 256 * 30
        sheet.col(2).width = 256 * 35
        sheet.col(3).width = 256 * 25
        sheet.col(4).width = 256 * 25
                
        if chart_account_id:
            chart_account_id = account_obj.browse(cr, uid, chart_account_id)
        company_id = (chart_account_id and chart_account_id.company_id and chart_account_id.company_id.id) or False

        #base
        min_period = False
        if period_ids:
            min_period = period_obj.search(cr, uid, [('id', 'in', period_ids)], order='date_stop Desc', limit=1)

        elif date_to:
            min_period = period_obj.search(cr, uid, [('date_stop', '<=', date_to)], order='date_stop Desc', limit=1)

        if not min_period:
            min_period = period_obj.search(cr, uid, [], order='date_stop Desc', limit=1)
        min_period = period_obj.browse(cr, uid, min_period[0])

#        raise osv.except_osv(_('Error !'), _('test'))
        company_qry = (company_id and ("AND amls.company_id = " + str(company_id) + " "))
        target_move_qry = (target_move and target_move == 'posted' and "AND ams.state = 'posted' ") or " "
        date_start_max_period = min_period and min_period.date_start or False
        date_stop_max_period = min_period and min_period.date_stop or False
        
        account_qry = (account_ids and ((len(account_ids) == 1 and "AND amls.account_id = " + str(account_ids[0]) + " ") or "AND amls.account_id IN " + str(tuple(account_ids)) + " ")) or "AND amls.account_id IN (0) "

        cr.execute(
                "select aa.id, aa.code, aa.name, " \
                "coalesce( " \
                    "(select sum(aml.debit- aml.credit) from account_move_line aml " \
                    "where aml.date <= '" + str(date_start_max_period) + "' and aml.account_id = aa.id group by aml.account_id) " \
                ",0) as opening_balance, " \
                "coalesce( " \
                    "(select sum(aml.debit) from account_move_line aml " \
                    "where aml.date >= '" + str(date_start_max_period) + "' and aml.date <= '" + str(date_stop_max_period) + "' and aml.account_id = aa.id group by aml.account_id) " \
                ",0) as current_debit, " \
                "coalesce( " \
                    "(select sum(aml.credit) from account_move_line aml " \
                    "where aml.date >= '" + str(date_start_max_period) + "' and aml.date <= '" + str(date_stop_max_period) + "' and aml.account_id = aa.id group by aml.account_id) " \
                ",0) as current_credit, " \
                "coalesce( " \
                    "(select sum(aml.debit- aml.credit) from account_move_line aml " \
                    "where aml.date <= '" + str(date_stop_max_period) + "' and aml.account_id = aa.id group by aml.account_id) " \
                ",0) as balance " \
                "from account_account aa where id in (select DISTINCT account_id from account_move_line amls inner join account_move ams " \
                "on amls.move_id = ams.id where amls.date <= '" + str(date_stop_max_period) + "' " \
                + account_qry \
                + company_qry \
                + target_move_qry + \
                "order by account_id)")
        qry = cr.dictfetchall()
        
        if qry:
            for r in qry:
                debit_total += r['current_debit'] or 0.00
                credit_total += r['current_credit'] or 0.00
                open_balance += r['opening_balance'] or 0.00
                balance += r['balance'] or 0.00
                results.append({
                    'code' : r['code'],
                    'name' : r['name'],
                    'opening_balance' : r['opening_balance'],
                    'debit' : r['current_debit'],
                    'credit' : r['current_credit'],
                    'balance' : r['balance'],
                    })
                
        results1 = results and sorted(results, key=lambda val_res: val_res['code']) or []
        
        
        row+=1
        sheet.row(row).height = 100
        sheet.write_merge(row,row, 0, 7, '', text_tstyle_c)
        

        for rs1 in results1:
            
            row += 1
            sheet.write(row, 0,str(rs1['code']), view_tstyle_l)
            sheet.write(row, 1,str(rs1['name']), view_tstyle_l)
            sheet.write(row, 2,rs1['opening_balance'], currency_format)
            sheet.write(row, 3,rs1['debit'], currency_format)
            sheet.write(row, 4,rs1['balance'], currency_format)
            
            row += 1
            sheet.write(row, 0,'', view_tstyle_l)
            sheet.write(row, 1,'', view_tstyle_l)
            sheet.write(row, 2,'', view_tstyle_l)
            sheet.write(row, 3,rs1['credit'], currency_format)
            sheet.write(row, 4,'', view_tstyle_r)
                                       
        row += 1
        sheet.write(row, 0,'Report Total :', currency_ttl_format_top)
        sheet.write(row, 1,'', currency_ttl_format_top)
        sheet.write(row, 2,open_balance, currency_ttl_format_top)
        sheet.write(row, 3,debit_total, currency_ttl_format_top)
        sheet.write(row, 4,balance, currency_ttl_format_top)
        
        row += 1
        sheet.write(row, 0,'', currency_ttl_format_bottom)
        sheet.write(row, 1,'', currency_ttl_format_bottom)
        sheet.write(row, 2,'', currency_ttl_format_bottom)
        sheet.write(row, 3,credit_total, currency_ttl_format_bottom)
        sheet.write(row, 4,'', currency_ttl_format_bottom)
        
        row += 1
        sheet.row(row).height = 100
        sheet.write_merge(row, row, 0, 4, '')
        
         
        row += 1
        sheet.write(row, 0, 'End of Report ', text_tstyle)
        #csv_content = ''
        
        stream = cStringIO.StringIO()
        workbook.save(stream)
    
            
        
        cr.execute(""" DELETE FROM output""")
        attach_id = self.pool.get('output').create(cr, uid, {'name':'Trial Report.xls', 'xls_output':base64.encodestring(stream.getvalue())})
        return {
                'name': _('Notification'),
                'context': context,
                'view_type': 'form',
                'view_mode': 'form',
                'res_model': 'output',
                'res_id':attach_id,
                'type': 'ir.actions.act_window',
                'target':'new'
                }  
        
    
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
